﻿using HtmlAgilityPack;
using log4net;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace GetAllCityCode
{
    public class City
    {
        public decimal ID { get; set; }
        public string Name { get; set; }
        public string Code { get; set; }
        public string Org_Level { get; set; }
        public string ParentCode { get; set; }
        public decimal ParentID { get; set; }
        public string Contry { get; set; }
        public string Loc_x { get; set; }
        public string Loc_y { get; set; }
    }

    public class HttpHelper
    {
        private static ILog log = log4net.LogManager.GetLogger(typeof(HttpHelper));

        public static string DownloadHtml(string url, Encoding encod)
        {
            string html = string.Empty;
            try
            {
                //设置请求参数
                HttpWebRequest request = HttpWebRequest.Create(url) as HttpWebRequest;
                request.Timeout = 10 * 1000;//10s超时
                request.ContentType = "text/html;charset=utf-8";
                request.UserAgent = "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36";

                //获取结果
                using (HttpWebResponse resp = request.GetResponse() as HttpWebResponse)
                {
                    if (resp.StatusCode != HttpStatusCode.OK)
                    {
                        log.Fatal(string.Format("抓取{0}地址返回失败,response.StatusCode = {1}", url, resp.StatusCode));
                    }
                    else
                    {
                        try
                        {
                            StreamReader sr = new StreamReader(resp.GetResponseStream(), encod);
                            html = sr.ReadToEnd();
                            sr.Close();
                        }
                        catch (Exception e)
                        {
                            log.Fatal(string.Format("DownLoadHtml抓取html{0}保存失败", url), e);

                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (e.Message.Equals("远程服务器返回错误：(306)。"))
                {
                }
                log.Fatal(e);
            }
            finally
            {
            }
            return html;
        }
    }


    public class SQLHelper
    {

        /// 一个有效的数据库连接对象 
        /// 命令类型(存储过程,命令文本或其它.) 
        /// T存储过程名称或T-SQL语句 
        /// SqlParamter参数数组 
        /// 返回影响的行数 
        public static int ExecuteNonQueryForCity(List<City> cityList)
        {
            int count = 0;
            //string dbConnectStr = System.Configuration.ConfigurationSettings.AppSettings["CityConnectionString"].ToString();
            //var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
            string dbConnectStr = System.Configuration.ConfigurationManager.ConnectionStrings["GetAllCityCode.Properties.Settings.CityConnectionString"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(dbConnectStr))
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                // 创建SqlCommand命令,并进行预处理 
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.CommandText = "insert into base_city(ID,name,Code,Contry,Loc_x,Loc_y,Org_Level,ParentCode,ParentID,state) values(@ID,@name,@Code,@Contry,@Loc_x,@Loc_y,@Org_Level,@ParentCode,@ParentID,@state)";
                    foreach (var city in cityList)
                    {
                        try
                        {
                            if (string.IsNullOrEmpty(city.Name))
                                city.Name = "";
                            if (string.IsNullOrEmpty(city.Code))
                                city.Code = "";
                            if (string.IsNullOrEmpty(city.Contry))
                                city.Contry = "";
                            if (string.IsNullOrEmpty(city.Loc_x))
                                city.Loc_x = "";
                            if (string.IsNullOrEmpty(city.Loc_y))
                                city.Loc_y = "";
                            if (string.IsNullOrEmpty(city.Org_Level))
                                city.Org_Level = "";
                            if (string.IsNullOrEmpty(city.ParentCode))
                                city.ParentCode = "";

                            cmd.Parameters.Add(new SqlParameter("@ID", city.ID));
                            cmd.Parameters.Add(new SqlParameter("@name", city.Name));
                            cmd.Parameters.Add(new SqlParameter("@Code", city.Code));
                            cmd.Parameters.Add(new SqlParameter("@Contry", city.Contry));
                            cmd.Parameters.Add(new SqlParameter("@Loc_x", city.Loc_x));
                            cmd.Parameters.Add(new SqlParameter("@Loc_y", city.Loc_y));
                            cmd.Parameters.Add(new SqlParameter("@Org_Level", city.Org_Level));
                            cmd.Parameters.Add(new SqlParameter("@ParentCode", city.ParentCode));
                            cmd.Parameters.Add(new SqlParameter("@ParentID", city.ParentID));
                            cmd.Parameters.Add(new SqlParameter("@state", "1"));
                            // Finally, execute the command 
                            int retval = cmd.ExecuteNonQuery();
                            if (retval == 0)
                            {
                                Console.WriteLine("插入错误：");
                            }
                            count += retval;
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("插入错误：" + e.Message);
                        }
                        // 清除参数,以便再次使用. 
                        cmd.Parameters.Clear();
                    }
                }
                connection.Close();
            }
            return count;
        }
    }
    

    public class 省市县数据抓取
        {
            private ILog log = log4net.LogManager.GetLogger(typeof(省市县数据抓取));
            public const string UrlStr = "http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/201703/t20170310_1471429.html";
            public List<City> SaveList = new List<City>();
            public 省市县数据抓取()
            {
                try
                {
                    log.Info("抓取数据");
                    string HtmlStr = HttpHelper.DownloadHtml(UrlStr, Encoding.UTF8);
                    HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
                    doc.LoadHtml(HtmlStr);
                    //string goodsListPath = "//*[@id='J_goodsList']";
                    //HtmlNode goodsListNode = doc.DocumentNode.SelectSingleNode(goodsListPath);
                    string liPath = "//p[@class='MsoNormal']";
                    HtmlNodeCollection goodsNodeCollection = doc.DocumentNode.SelectNodes(liPath);

                    City c = new City()
                    {
                        ID = 1,
                        Name = "全国",
                        Code = "100000",
                        Contry = "China",
                        Org_Level = "1"
                    };
                    SaveList.Add(c);
                    foreach (var item in goodsNodeCollection)
                    {
                        var firstNode = item.FirstChild;
                        if (firstNode.Name == "b")
                            GetProvince(item);
                        else if (firstNode.InnerText == "　")
                        {
                            GetCity(item);
                        }
                        else if (firstNode.InnerText == "　　")
                        {
                            GetCounty(item);
                        }
                    }

                }
                catch (Exception e)
                {
                    log.Info("last child code:" + SaveList.Last().Code);
                    log.Info(e);
                    throw (e);
                }
            }

            private void GetCounty(HtmlNode item)
            {
                City c = new City();
                c.Code = Regex.Replace(item.ChildNodes[1].InnerText, "&nbsp;", "");
                c.Name = item.ChildNodes[2].InnerText.Trim();
                c.Org_Level = "4";
                c.ID = SaveList.Last().ID + 1;
                var pc = SaveList.Last(i => i.Org_Level == "3");
                c.ParentCode = pc.Code;
                c.ParentID = pc.ID;
                c.Contry = "China";
                //if(c.Name == "市辖区")
                //  return;
                SaveList.Add(c);
            }

            private void GetCity(HtmlNode item)
            {
                City c = new City();
                c.Code = Regex.Replace(item.ChildNodes[1].InnerText.Trim(), "&nbsp;","");
                c.Name = item.ChildNodes[2].InnerText.Trim();
                c.Org_Level = "3";
                c.ID = SaveList.Last().ID + 1;
                var pc = SaveList.Last(i => i.Org_Level == "2");
                c.ParentCode = pc.Code;
                c.ParentID = pc.ID;
                c.Contry = "China";
                SaveList.Add(c);

            }

            private void GetProvince(HtmlNode item)
            {
                City c = new City();
                c.Code =Regex.Replace(item.ChildNodes[0].FirstChild.InnerText, "&nbsp;","");
                c.Name = item.ChildNodes[1].FirstChild.InnerText.Trim();
                c.Org_Level = "2";
                c.ID = SaveList.Last().ID + 1;
                var pc = SaveList.Last(i => i.Org_Level == "1");
                c.ParentCode = pc.Code;
                c.ParentID = pc.ID;
                c.Contry = "China";
                SaveList.Add(c);
            }

            public void Save()
            {
                log.Info("保存数据");
                SQLHelper.ExecuteNonQueryForCity(SaveList);
            }
        }
 }
